package cn.iocoder.yudao.framework.excel.core.util.poi.annotation;

import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelDropdown {
    /* *
     * @Author
     * @Description 为excel工作表添加省市级联下拉框
     * @Date 11:51 2020/12/10
     * @Param [book 工作表, pullDownStartRow 下拉框起始行, pullDownEndRow 下拉框终止行, provinceCol 省份列, areaMap 以省份为key，城市数组为value] 行和列都是以0开始
     * @return void
     **/
    public static void cascade(Workbook book, int pullDownStartRow, int pullDownEndRow, int provinceCol, String[] option1 , List<String[]> areaList, String sheetName, String[]... options) {
        // 获取需要用户填写的sheet（数据页，这里暂时为0，不开放，需要的时候再改）
        XSSFSheet sheetPro = (XSSFSheet) book.getSheetAt(0);
        // 获取省份（key）
//        String[] provinceArr = areaMap.keySet().toArray(new String[0]);
        String[] provinceArr = option1;
        // 有子集的地址（可拓展为三级级联）
        String[] areaFatherNameArr = provinceArr;
//------------------------------------------------------------隐藏sheet开始（不需要修改）------------------------
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        // sheetName 例子中为area
        Sheet hideSheet = book.createSheet(sheetName);
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);
        int rowId = 0;
        // 设置第一行，存省的信息
        Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue("一级列表");
        for (int i = 0; i < provinceArr.length; i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr[i].replaceAll("、",""));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (int i = 0; i < areaFatherNameArr.length; i++) {
            String key = areaFatherNameArr[i].replaceAll("、","");
//            String[] son = areaMap.get(key);
            String[] son = areaList.get(i);
            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int j = 0; j < son.length; j++) {
                Cell cell = row.createCell(j + 1);
                cell.setCellValue(son[j]);
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.length);
            Name name = book.createName();
            // 解决首字符只能是字母或者下划线问题
            if (!Character.isLetter(key.charAt(0))) {
                key = "_" + key;
            }
            //key不可重复
            name.setNameName(key);
            String formula = sheetName + "!" + range;
            name.setRefersToFormula(formula);
        }
        //------------------------------------------------------------隐藏sheet结束（不需要修改）------------------------

        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheetPro);
        // 省规则
        DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(sheetName + "!" + getRange(1, 1, areaFatherNameArr.length));
        //DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(areaFatherNameArr);
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(pullDownStartRow, pullDownEndRow, provinceCol, provinceCol);
        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
        //验证
        provinceDataValidation.createErrorBox("error", "请选择正确的值");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheetPro.addValidationData(provinceDataValidation);

        //对行设置有效性（endRow需要多加1才对的上省级级联共同的行数）
        for (int i = pullDownStartRow + 1; i < pullDownEndRow + 2; i++) {
            setDataValidation(transCharByNum(provinceCol), sheetPro, i, provinceCol + 2);
        }

//        String[] values = {"Option 1", "Option 2", "Option 3"}; /** 创建数据验证对象*/
//        DataValidationHelper validationHelper = sheetPro.getDataValidationHelper();
//        DataValidationConstraint constraint1 = validationHelper.createExplicitListConstraint(values); /** 设置下拉列表的位置*/
//        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 2, 2);
//        DataValidation validation = validationHelper.createValidation(constraint1, addressList); /** 将数据验证对象应用到单元格*/
//        sheetPro.addValidationData(validation); /** 将Excel文档保存到文件*/
//
//        String[] values2 = {"Option 21", "Option 22", "Option 23"}; /** 创建数据验证对象*/
//        DataValidationHelper validationHelper2 = sheetPro.getDataValidationHelper();
//        DataValidationConstraint constraint2 = validationHelper.createExplicitListConstraint(values2); /** 设置下拉列表的位置*/
//        CellRangeAddressList addressList2 = new CellRangeAddressList(0, 0, 3, 3);
//        DataValidation validation2 = validationHelper2.createValidation(constraint2, addressList2); /** 将数据验证对象应用到单元格*/
//        sheetPro.addValidationData(validation2); /** 将Excel文档保存到文件*/
        int i = 0;
        for (String[] array: options
             ) {
            String[] values = array; /** 创建数据验证对象*/
            DataValidationHelper validationHelper = sheetPro.getDataValidationHelper();
            DataValidationConstraint constraint1 = validationHelper.createExplicitListConstraint(values); /** 设置下拉列表的位置*/
            CellRangeAddressList addressList = new CellRangeAddressList(pullDownStartRow, pullDownEndRow, 2+i, 2+i);
            DataValidation validation = validationHelper.createValidation(constraint1, addressList); /** 将数据验证对象应用到单元格*/
            validation.createErrorBox("error", "请选择正确的值");
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            sheetPro.addValidationData(validation); /** 将Excel文档保存到文件*/
            i++;
        }


    }

    /* *
     * @Author
     * @Description 输入0返回A 输入1返回B
     * @Date 11:17 2020/12/10
     * @Param [num]
     * @return java.lang.String
     **/
    public static String transCharByNum(Integer num) {
        return new Character((char) (num.toString().getBytes()[0] + 17)).toString();
    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula(
                "INDIRECT($" + offset + "$" + (rowNum) + ")", rowNum, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    /* *
     * @Author
     * @Description 定义公式校验
     * @Date 12:43 2020/12/10
     * @Param [formulaString, naturalRowIndex, naturalColumnIndex, dvHelper]
     * @return org.apache.poi.ss.usermodel.DataValidation
     **/
    private static DataValidation getDataValidationByFormula(
            String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        //如果A2是江苏省，那么此处就是江苏省下的市信息。
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColumnIndex - 1;
        int lastCol = naturalColumnIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, firstCol, lastCol);
        // 数据有效性对象
        // 绑定
        XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof XSSFDataValidation) {
            data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        //data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return data_validation_list;
    }

    /**
     * 计算formula（因为下拉框poi导出用createExplicitListConstraint(arr)最多只能有52个，所以需要更换方法，
     * 更换为createFormulaListConstraint(listFormula).传入参数是一个引用，引用某张表的连续数据，所以应该是传入$B$1:$K$1类似的值）
     * 此方法为计算传入参数
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        // 25和26以及51是因为Excel的列名是从A-Z,然后AA-AZ，然后BA-BZ以此类推，每过26个字母需要做不同的处理，所以这些常数这里不用改
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

    public static void main(String[] args) {
        Map<String,String []> map = new HashMap<>();
        map.put("一级字段1",new String[]{"二级字段1.1","二级字段1.2","二级字段1.3"});
        map.put("一级字段2",new String[]{"二级字段2.1","二级字段2.2","二级字段2.3","二级字段2.4"});
        XSSFWorkbook sheets = new XSSFWorkbook();
        XSSFSheet aaa = sheets.createSheet("sheet1");
//        aaa.createRow(0).createCell(0).setCellValue(0);
        // 创建下拉列表的选项
        String[] options = new String[]{"Option1", "Option2", "Option3"};
        String[] options2 = new String[]{"Option11", "Option22", "Option33"};

//        cascade(sheets,0,100,0,map,"hide" ,options, options2);
        FileOutputStream os = null;
        try {
            os = new FileOutputStream("D:\\wangxp\\excel\\testabc.xlsx");
            sheets.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(os);
        }


    }

//    public static void main(String[] args) throws IOException {
//        Workbook workbook = new HSSFWorkbook();
//        Sheet sheet = workbook.createSheet("Sheet1");
//
//        // 创建下拉列表的选项
//        String[] options = new String[]{"Option1", "Option2", "Option3"};
//
//        // 定义下拉列表的单元格范围
//        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(0, 0, 0, 0); // A1单元格
//
//        // 创建下拉列表
//        DataValidationHelper helper = sheet.getDataValidationHelper();
//        DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
//        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//
//        // 设置下拉列表
//        dataValidation.setSuppressDropDownArrow(false);
//        dataValidation.setShowErrorBox(true);
//        sheet.addValidationData(dataValidation);
//
//        // 写入文件
//        FileOutputStream fileOut = new FileOutputStream("D:\\wangxp\\excel\\dropdown.xls");
//        workbook.write(fileOut);
//        fileOut.close();
//
//        workbook.close();
//    }

//    public static void main(String[] args) {
//        try (Workbook workbook = new XSSFWorkbook()) {
//            Sheet sheet = workbook.createSheet("Sheet1"); /** 设置下拉列表的选项 */
//            String[] values = {"Option 1", "Option 2", "Option 3"}; /** 创建数据验证对象*/
//            DataValidationHelper validationHelper = sheet.getDataValidationHelper();
//            DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(values); /** 设置下拉列表的位置*/
//            CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
//            DataValidation validation = validationHelper.createValidation(constraint, addressList); /** 将数据验证对象应用到单元格*/
//            sheet.addValidationData(validation); /** 将Excel文档保存到文件*/
//            try (FileOutputStream fileOut = new FileOutputStream("D:\\wangxp\\excel\\output.xlsx")) {
//                workbook.write(fileOut);
//            }
//            System.out.println("Excel生成成功！");
//        } catch (IOException e) {
//            System.err.println("生成Excel时出现错误：" + e.getMessage());
//        }
//    }
}